#include "AdDao.h"
#include <sqlite3.h>
#include <stdlib.h>
#include <stdio.h>
#include "glib.h"
int saveAd(Ad *ad){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return -1;
    }

    //执行
    char sql[256];
    sprintf(sql,"insert into t_ad(id,`index`,start_time,end_time) values(%d,%d,'%s','%s');",ad->id,ad->index,ad->start_time,ad->end_time);
    char *errorMsg;   
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }


    //释放
    sqlite3_close(db);

    return 0;
}
int updateAd(Ad *ad){
     sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return -1;
    }

    //执行
    char sql[256];
    sprintf(sql,"update t_ad set `index`=%d,start_time='%s',end_time='%s';",ad->index,ad->start_time,ad->end_time);
    char *errorMsg;   
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }


    //释放
    sqlite3_close(db);

    return 0;
}
Ad* getAd(int id){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return NULL;
    }

    //执行
    // char sql[256];
    // sprintf(sql,"select * from t_user where id = %d;",id);
    char *errorMsg;   

    char** pResult;  
    int nRow;
    int nCol;
    char sql[256];
    sprintf(sql,"select * from t_ad where id = %d;",id);
    retVal = sqlite3_get_table(db,sql,&pResult,&nRow,&nCol,&errorMsg);
    if (retVal != SQLITE_OK)
    { 
        printf("%s\n",errorMsg);
    }

     int  index = nCol;    
    // dbResult 第一行数据是字段名称，从 nColumn 索引开始才是真正的数据
    printf( "查到ad%d条记录\n", nRow );

    for(  int i = 0; i < nRow ; i++ )
    {
         //printf("ad第 %d 条记录\n", i+1 );
         Ad *ad = calloc(1,sizeof(Ad));
         for( int j = 0 ; j < nCol; j++ )
         {
              char *colName =  pResult[j];
               char *colVal =  pResult [index];
              //printf( "ads字段名:%s  >  字段值:%s\n",  pResult[j], pResult [index] ); 

              if (g_str_equal("id",colName))
              {
                 ad->id = atoi(colVal);
              }

               if (g_str_equal("index",colName))
              {
                ad->index = atoi(colVal);
              }
                 
                  if (g_str_equal("start_time",colName))
              {
                ad->start_time = colVal;
              }
                 
                  if (g_str_equal("end_time",colName))
              {
                ad->end_time = colVal;
              }
              ++index;
              
              
         }
         //printf( "--------\n" );
         return ad;
    }

    return NULL;
}


int saveAr(AdResource *ar){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return -1;
    }

    //执行
    char sql[256];
    sprintf(sql,"insert into t_ad_resource(url,`index`,ad_id,sign,type_id) values('%s',%d,%d,'%s',%d);",ar->path,ar->index,ar->ad_id,ar->sign,ar->type_id);
    g_print("%s\n",sql);
    char *errorMsg;   
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }


    //释放
    sqlite3_close(db);

    return 0;
}
int updateAr(AdResource *ar){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return -1;
    }

    //执行
    char sql[256];
    sprintf(sql,"update t_ad_resource set `index`=%d,url='%s',sign='%s',ad_id=%d,type_id=%d where id = %d;",ar->index,ar->path,ar->sign,ar->ad_id,ar->type_id,ar->id);
    char *errorMsg;   
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }


    //释放
    sqlite3_close(db);

    return 0;

}
AdResource* getAr(int adid,int indexTmp){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return NULL;
    }


    char *errorMsg;   
    char** pResult;  
    int nRow;
    int nCol;
    char sql[256];
    sprintf(sql,"select * from t_ad_resource where ad_id = %d and `index`= %d;",adid,indexTmp);
    retVal = sqlite3_get_table(db,sql,&pResult,&nRow,&nCol,&errorMsg);
    if (retVal != SQLITE_OK)
    { 
        printf("%s\n",errorMsg);
    }

     int  index = nCol;    
    // dbResult 第一行数据是字段名称，从 nColumn 索引开始才是真正的数据
    printf( "查到%d条记录\n", nRow );

    for(  int i = 0; i < nRow ; i++ )
    {
         //printf("第 %d 条记录\n", i+1 );
         AdResource *ar = calloc(1,sizeof(Ad));
         for( int j = 0 ; j < nCol; j++ )
         {
              char *colName =  pResult[j];
               char *colVal =  pResult [index];
              //printf( "字段名:%s  >  字段值:%s\n",  pResult[j], pResult [index] ); 

              if (g_str_equal("id",colName))
              {
                 ar->id = atoi(colVal);
              }

               if (g_str_equal("index",colName))
              {
                ar->index = atoi(colVal);
              }
                 
                  if (g_str_equal("ad_id",colName))
              {
                ar->ad_id = atoi(colVal);
              }

                  if (g_str_equal("type_id",colName))
              {
                ar->type_id = atoi(colVal);
              }
                 
                  if (g_str_equal("sign",colName))
              {
                g_print("%s==========================\n",colVal);
                ar->sign = colVal;
              }

              if (g_str_equal("url",colName))
              {
                ar->path = colVal;
              }
              ++index;
            
              
         }
         //printf( "--------\n" );
         return ar;
    }

    return NULL;
}

int clearExpireAd(){

    
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return -1;
    }

    //执行
    char sql[256];

    sprintf(sql,"delete from t_ad where end_time < datetime(CURRENT_TIMESTAMP,'localtime');");
    char *errorMsg;   
    //删除广告
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    //删除广告资源--删除没有广告的资源
    //delete from t_ad_resource ad_id not in select id form t_ad;
     if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }

    sprintf(sql,"delete from t_ad_resource where ad_id not in (select id from t_ad);");   
    //删除资源
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    { 
        printf("%s\n",errorMsg);
        return -1;
    }

    //删除本地路径文件


    //释放
    sqlite3_close(db);

   

}